package cn.explink.dop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeSet;

import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.explink.dop.entity.order.StoDelEntity;
import cn.explink.dop.tools.Tools;
import cn.explink.eap.core.common.dao.impl.GenericBaseCommonDao;

/**
 * 店面&业务人员管理dao层
 * @author firefly 2015-2-28 14:49
 *
 */
@Repository("stoDelDao")
public class StoDelDao extends GenericBaseCommonDao<StoDelEntity, String>{
	/**
	 * 添加数据
	 * @param res 添加对象
	 * @return 添加成功之后对象
	 */
	public StoDelEntity add(final StoDelEntity sd) {
		try {
			final String sql = "insert into bd_sto_del (restaurant_id,deliveryman_id) " +
					"values(?,?)";
			KeyHolder keyHolder = new GeneratedKeyHolder();
			this.getJdbcTemplate().update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(sql, new String[] { "id" });
					ps.setLong(1, sd.getRestaurantId());
					ps.setLong(2, sd.getDeliverymanId());
					return ps;
				}
			}, keyHolder);
			sd.setId(keyHolder.getKey().longValue());
			return sd;
		} catch (Exception e) {
			return new StoDelEntity();
		}
	}

	/**
	 * 修改基本信息
	 * @param baseInfo 基本信息对象
	 */
	public void modify(final StoDelEntity sd) {
		StringBuffer sql = new StringBuffer("update bd_sto_del set ");
		if(0 != sd.getRestaurantId())
			sql.append("restaurant_id=").append(sd.getRestaurantId()).append(",");
		if(0 != sd.getDeliverymanId())
			sql.append("deliveryman_id=").append(sd.getDeliverymanId());
		sql.append(" ").append("where id=").append(sd.getId());
		super.getJdbcTemplate().update(sql.toString());
	}
	
	/**
	 * 按照实体查询，无分页
	 * @param res 查询实体
	 * @return 查询列表
	 */
	public List<StoDelEntity> finds(final StoDelEntity sd) {
		StringBuffer sql = new StringBuffer("select * from bd_sto_del ");
		if (null != sd)
			sql.append("where");
		if(0 != sd.getRestaurantId())
			sql.append(" and restaurant_id=").append(sd.getRestaurantId());
		if(0 != sd.getDeliverymanId())
			sql.append(" and deliveryman_id='").append(sd.getDeliverymanId());
		String sqlStr = sql.toString();
		if (sqlStr.contains("where and"))
			sqlStr = sqlStr.replace("where and", "where");
		else if (sqlStr.contains("where"))
			sqlStr = sqlStr.replace("where", "");
		List<Map<String, Object>> mapList = super.getJdbcTemplate().queryForList(sqlStr);
		List<StoDelEntity> resList = new ArrayList<StoDelEntity>();
		for (Map<String, Object> m : mapList) {
			StoDelEntity s = (StoDelEntity) Tools.convertMap(StoDelEntity.class, m);
			resList.add(s);
		}
		return resList;
	}
	
	/**
	 * 按照实体获取分页数据
	 * @param baseInfo 查询实体
	 * @param pageNum 当前页
	 * @param pageSize 当前页记录条数
	 * @return 查询结果集
	 */
	public Map<String, Object> findByPage(final StoDelEntity sd, final int pageNum, final int pageSize) {
		StringBuffer sql = new StringBuffer("select * from bd_sto_del ");
		if (null != sd)
			sql.append("where");
		if(0 != sd.getRestaurantId())
			sql.append(" and restaurant_id=").append(sd.getRestaurantId());
		if(0 != sd.getDeliverymanId())
			sql.append(" and deliveryman_id=").append(sd.getDeliverymanId());
		String sqlStr = sql.toString();
		if (sqlStr.contains("where and"))
			sqlStr = sqlStr.replace("where and", "where");
		else if (sqlStr.contains("where"))
			sqlStr = sqlStr.replace("where", "");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("count", super.getJdbcTemplate().queryForLong(sqlStr.replace("*", "count(id)")));
		sql.append(" limit ").append((pageNum - 1) * pageSize).append(",").append(pageSize);
		List<Map<String, Object>> mapList = super.getJdbcTemplate().queryForList(sqlStr);
		List<StoDelEntity> resList = new ArrayList<StoDelEntity>();
		for (Map<String, Object> m : mapList) {
			StoDelEntity s = (StoDelEntity) Tools.convertMap(StoDelEntity.class, m);
			Map<String, Object> restaurant = null;
			Map<String, Object> deliveryman = null;
			try{
				restaurant = this.getJdbcTemplate().queryForMap("select sto_name from bd_storefront where id=" + s.getRestaurantId());
				s.setRestaurantName((String) restaurant.get("sto_name"));
			}catch(DataAccessException e){
				s.setRestaurantName("");
			}
			
			try{
				deliveryman = this.getJdbcTemplate().queryForMap("select del_name from bd_deliveryman where id=" + s.getDeliverymanId());
				s.setDeliverymanName((String) deliveryman.get("del_name"));
			}catch(DataAccessException e){
				s.setDeliverymanName("");
			}
			resList.add(s);
		}
		map.put("list", resList);
		return map;
	}
	
	/**
	 * 删除选中对象
	 * @param baseInfoList 参数集合
	 */
	public int delete(final List<StoDelEntity> sdList) {
		StringBuffer sql = new StringBuffer("delete from bd_sto_del");
		TreeSet<Long> set = new TreeSet<Long>();
		for (StoDelEntity s : sdList) {
			set.add(s.getId());
		}
		sql.append(" ").append("where id in (").append(StringUtils.join(set.toArray(), ",")).append(")");
		return super.getJdbcTemplate().update(sql.toString());
	}
}
